const express = require('express');
const router = express.Router();
const { sequelize } = require('../config/database');
const Product = require('../models/Product');
const Order = require('../models/Order');
const Review = require('../models/Review');
const { Op } = require('sequelize');

// 获取销售趋势
router.get('/sales-trend', async (req, res) => {
    try {
        const { period = 'week' } = req.query;
        let dateFormat, interval;

        switch (period) {
            case 'day':
                dateFormat = '%Y-%m-%d';
                interval = 7;
                break;
            case 'week':
                dateFormat = '%Y-%u';
                interval = 4;
                break;
            case 'month':
                dateFormat = '%Y-%m';
                interval = 6;
                break;
            default:
                dateFormat = '%Y-%m-%d';
                interval = 7;
        }

        const salesTrend = await sequelize.query(`
            SELECT 
                DATE_FORMAT(order_date, :dateFormat) as period,
                COUNT(*) as order_count,
                SUM(total_amount) as total_revenue
            FROM orders
            WHERE order_date >= DATE_SUB(NOW(), INTERVAL :interval ${period === 'day' ? 'DAY' : period === 'week' ? 'WEEK' : 'MONTH'})
            GROUP BY DATE_FORMAT(order_date, :dateFormat)
            ORDER BY period ASC
        `, {
            replacements: { dateFormat, interval },
            type: sequelize.QueryTypes.SELECT
        });

        res.json(salesTrend);
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

// 获取热门商品
router.get('/top-products', async (req, res) => {
    try {
        const { limit = 10 } = req.query;
        
        const topProducts = await sequelize.query(`
            SELECT 
                p.name,
                p.price,
                COUNT(oi.product_id) as order_count,
                SUM(oi.quantity) as total_sold,
                SUM(oi.quantity * oi.price) as total_revenue
            FROM products p
            JOIN (
                SELECT 
                    json_extract(value, '$.product_id') as product_id,
                    json_extract(value, '$.quantity') as quantity,
                    json_extract(value, '$.price') as price
                FROM orders, json_each(items)
            ) oi ON p.woo_id = oi.product_id
            GROUP BY p.id
            ORDER BY total_sold DESC
            LIMIT :limit
        `, {
            replacements: { limit: parseInt(limit) },
            type: sequelize.QueryTypes.SELECT
        });

        res.json(topProducts);
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

// 获取客户画像
router.get('/customer-insights', async (req, res) => {
    try {
        const insights = await sequelize.query(`
            SELECT 
                COUNT(DISTINCT customer_email) as total_customers,
                AVG(total_amount) as avg_order_value,
                COUNT(*) as total_orders,
                SUM(total_amount) as total_revenue,
                COUNT(CASE WHEN status = 'completed' THEN 1 END) as completed_orders,
                COUNT(CASE WHEN status = 'pending' THEN 1 END) as pending_orders
            FROM orders
        `, {
            type: sequelize.QueryTypes.SELECT
        });

        res.json(insights[0]);
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

// 获取评论情感分析
router.get('/sentiment-analysis', async (req, res) => {
    try {
        const sentimentData = await Review.findAll({
            attributes: [
                'sentiment_label',
                [sequelize.fn('COUNT', sequelize.col('id')), 'count']
            ],
            group: ['sentiment_label']
        });

        res.json(sentimentData);
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

// 获取库存概览
router.get('/inventory-overview', async (req, res) => {
    try {
        const inventory = await sequelize.query(`
            SELECT 
                SUM(CASE WHEN stock_quantity = 0 THEN 1 ELSE 0 END) as out_of_stock,
                SUM(CASE WHEN stock_quantity <= 10 AND stock_quantity > 0 THEN 1 ELSE 0 END) as low_stock,
                SUM(CASE WHEN stock_quantity > 10 THEN 1 ELSE 0 END) as in_stock,
                SUM(stock_quantity * price) as inventory_value
            FROM products
            WHERE is_active = true
        `, {
            type: sequelize.QueryTypes.SELECT
        });

        res.json(inventory[0]);
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

module.exports = router;
